package com.xiyoufang.club.repository;

import com.xiyoufang.club.entity.Club;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public abstract interface ClubRepository
  extends JpaRepository<Club, Integer>, JpaSpecificationExecutor<Club>
{
  @Query(nativeQuery=true, value="SELECT * FROM club t WHERE t.name=:name  LIMIT 1")
  public abstract Club getClubByName(@Param("name") String paramString);
  
  @Query(nativeQuery=true, value="SELECT * FROM club t WHERE t.club_id=:clubId AND t.user_id=:userId LIMIT 1")
  public abstract Club getClubByIdAndUserId(@Param("clubId") int paramInt1, @Param("userId") int paramInt2);
  
  public abstract Club getClubById(Integer paramInteger);
  
  @Query(nativeQuery=true, value="SELECT * FROM club t WHERE club_id=:clubId LIMIT 1")
  public abstract Club getClubByClubId(@Param("clubId") Integer paramInteger);
  
  @Query(nativeQuery=true, value="SELECT * FROM club WHERE  user_id=:userId AND state=1 ORDER BY create_time DESC ")
  public abstract List<Club> findClubByUserId(@Param("userId") String paramInt);
  
  @Query(nativeQuery=true, value="SELECT c.name,c.nick_name,c.number,c.user_id,c.notice,c.club_id,c.auto_table_num,c.club_type,c.club_set,c.is_neg,m.type,m.is_partner,i.integral,m.level,m.p_user_id,m.cs_distri from club_members m right join club as c on c.state=1 and c.club_id=m.club_id right JOIN club_user_integral as i on m.club_id=i.club_id and m.user_id=i.user_id where m.user_id=:userId ")
  public abstract List findAllClubAndMemberByUserId(@Param("userId") int paramInt);
  
  @Query(nativeQuery=true, value="SELECT c.name,c.nick_name,c.number,c.user_id,c.notice,c.club_id,c.auto_table_num,c.club_type,c.club_set,c.is_neg,c.game_info,m.type,m.is_partner,i.integral,m.level,m.p_user_id,m.cs_distri from club_members m right join club as c on c.state=1 and c.club_id=m.club_id right JOIN club_user_integral as i on m.club_id=i.club_id and m.user_id=i.user_id where m.user_id=:userId and m.club_id=:clubId ")
  public abstract List findClubAndMemberByUserIdAndClubId(@Param("userId") int paramInt1, @Param("clubId") int paramInt2);
  
  @Query(nativeQuery=true, value="SELECT a.* FROM club a,club_members b  WHERE  a.club_id=b.club_id AND a.state=1 AND b.type!=1 AND b.user_id=:userId  ORDER BY a.create_time DESC ")
  public abstract List<Club> findJoinClubByUserId(@Param("userId") String paramInt);
  
  @Query(nativeQuery=true, value="SELECT a.* FROM club a,club_members b  WHERE  a.club_id=b.club_id AND a.state=1 AND b.user_id=:userId  ORDER BY a.create_time DESC ")
  public abstract List<Club> findCreateAndJoinClub(@Param("userId") String paramInt);
  
  @Query(nativeQuery=true, value="SELECT a.* FROM club a,club_members b  WHERE  a.club_id=b.club_id AND a.state=1 AND a.club_id=:clubId AND b.user_id=:userId  ORDER BY a.create_time DESC LIMIT 1")
  public abstract Club findCreateAndJoinClub(@Param("clubId") int paramInt1, @Param("userId") String paramInt2);
  
  @Query(nativeQuery=true, value="SELECT a.* FROM club a,club_members b  WHERE  a.club_id=b.club_id AND a.state=1   AND a.user_id!=:userId  AND b.user_id!=:userId  ORDER BY a.create_time DESC LIMIT 10")
  public abstract List<Club> findNotCreateAndJoinClubByUserId(@Param("userId") int paramInt);
  
  @Query(nativeQuery=true, value="SELECT club_id FROM club FOR UPDATE")
  public abstract List<Integer> findClubIdList();
  
  @Query(nativeQuery=true, value="SELECT a.* FROM club a  WHERE a.club_id=:clubId")
  public abstract Club findByClubId(@Param("clubId") int paramInt);
}
